package com.hellobike.haxing.modules.wms.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hellobike.haxing.modules.wms.dto.ExportReturnShippingDto;
import com.hellobike.haxing.modules.wms.dto.ExportShippingDto;
import com.hellobike.haxing.modules.wms.dto.ExportShippingUserInfoDto;
import com.hellobike.haxing.modules.wms.entity.ShippingHeader;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Description: t_wms_shipping_header
 * @Author: haxing-boot
 * @Date:   2020-02-02
 * @Version: V1.0
 */
public interface ShippingHeaderMapper extends BaseMapper<ShippingHeader> {

    @Select("SELECT sp.spu_name,sk.sku_code,sk.barcode,d.sku_count,d.box_count,d.no_box_count FROM(   " +
            "SELECT d.sku_id,\"sum\"(d.box_qty) as sku_count,    " +
            "\"sum\"(CASE WHEN d.box_id != -1 THEN 1 ELSE 0 END) as box_count,   " +
            "\"sum\"(CASE WHEN d.box_id = -1 THEN d.box_qty ELSE 0 END) as no_box_count    " +
            "FROM t_wms_delivery_order_box as d    " +
            "WHERE d.deleted=0 AND d.order_header_id =#{id}   " +
            "GROUP BY d.sku_id   " +
            ") as d " +
            "LEFT JOIN t_sku as sk ON sk.id=d.sku_id " +
            "LEFT JOIN t_spu as sp ON sp.id=sk.spu_id ")
    List<ExportShippingDto> exportShipping(@Param("id") Long id);

    @Select("SELECT sp.spu_name,sk.sku_code,sk.barcode,d.box_code,d.return_qty FROM( " +
            "SELECT d.sku_id,d.return_qty,d.box_code " +
            "FROM t_wms_return_warehouse_detail as d  " +
            "WHERE d.deleted=0 AND d.return_header_id =#{id} " +
            ") as d " +
            "LEFT JOIN t_sku as sk ON sk.id=d.sku_id " +
            "LEFT JOIN t_spu as sp ON sp.id=sk.spu_id")
    List<ExportReturnShippingDto> exportReturnShipping(@Param("id") Long id);

    @Select("SELECT d.create_time,d.order_code,t.shop_id,s.shop_name,o.bill_no,o.create_time as bill_create_time ," +
            "a.receiver,a.m_phone,sp.spu_name,od.num,od.price,od.dimension_values,od.product_amount-od.coupon_amount as productAmount FROM t_wms_order_relation as t " +
            "LEFT JOIN t_wms_delivery_order_header as d ON d.id=t.delivery_order_id " +
            "LEFT JOIN t_order as o ON o.id=t.user_order_id " +
            "LEFT JOIN t_shop as s ON s.id=t.shop_id " +
            "LEFT JOIN t_order_address as a ON a.order_id=o.id " +
            "LEFT JOIN t_order_detail as od ON od.order_id=o.id " +
            "LEFT JOIN t_spu as sp ON sp.id=od.product_id " +
            "WHERE t.deleted=0 AND d.deleted=0 AND o.deleted=0 AND od.deleted=0   AND d.id=#{id} " +
            "ORDER BY t.id DESC")
    List<ExportShippingUserInfoDto> exportShippingUserInfo(@Param("id") Long id);
}
